declare namespace msds = "http://matsadaml.org/msds/v1.0"; 
declare variable $ProductGroup as xs:string external;

document
{
processing-instruction {"xml-stylesheet"} {"type='text/xsl' href='../../System/Transformations/index_substances.xsl'"},
element index {
attribute productGroup {$ProductGroup},

let $companies := doc("../../ResourceFiles/CompanyDB.xml")
let $SubstanceDBs := doc("../../Substances/SubstanceDBs.xml")
let $SubstanceDB :=
    <DBs>
    {
    for $db in $SubstanceDBs/msds:substanceDBs/msds:db
    return
    	<DB key="{$db/@key}">
    		{doc(concat("../../Substances/", $db/msds:location))}
    	</DB>
    }
    </DBs>

let $Products := collection(concat("../../Products/", $ProductGroup))
for $dbKey in distinct-values(
        for $Subst in $Products/msds:product/msds:substances/msds:substance
        return concat($Subst/@db, "-", $Subst/@id)
    )
    let $Substance := ($Products/msds:product/msds:substances/msds:substance[concat(@db, "-", @id) = $dbKey])[1]
    let $substanceName := $SubstanceDB//DB[@key = $Substance/@db]//msds:substance-entry[@id = $Substance/@id]//msds:name[1]
    order by $dbKey
    return
        <substance db="{$Substance/@db}" key="{$Substance/@id}" name="{$substanceName}">
        {
        for $Product in  $Products
        let $id := $Product/msds:product/@id
        let $name := $Product/msds:product/msds:name[1]
        let $companyName := $companies//msds:company[@key = $Product//msds:company/@key]//msds:companyName
        where $Product/msds:product/msds:substances/msds:substance[@db = $Substance/@db and @id = $Substance/@id]
        order by $name
        return
            <product id="{$id}" name="{$name}" companyKey="{$Product//msds:company/@key}" companyName="{$companyName}"/>
        }
        </substance>
}
}
